package com.jack.bookshop.dao;

import com.jack.bookshop.util.DatabaseUtil;
import com.jack.bookshop.vo.CartVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CartDao {
    // 查看购物车
    public List<CartVo> getList(int userId) {
        List<CartVo> list = new ArrayList<CartVo>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql = "select * from cart where user_id=?";
        try {
            conn = DatabaseUtil.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, userId);
            rs = pst.executeQuery();
            while(rs.next()) {
                CartVo vo = new CartVo();
                vo.setId(rs.getInt("id"));
                vo.setBook_id(rs.getInt("book_id"));
                vo.setQuantity(rs.getInt("quantity"));
                vo.setChecked(rs.getInt("checked"));
                vo.setBook_title(rs.getString("book_title"));
                vo.setBook_image_url(rs.getString("book_image_url"));
                vo.setBook_price(rs.getBigDecimal("book_price"));
                list.add(vo);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        return list;
    }

    // 设置购物车中某一本图书为选中/未选中状态
    public int updateChecked(int cartId, int checked) {
        int i = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "update cart set checked = ? where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, checked);
            pst.setInt(2, cartId);
            i = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        return i;
    }

    // 更新购物车中某一本图书的数量 quantity为+1或-1
    public int updateQuantity(int cartId, int quantity) {
        int i = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "update cart set quantity = quantity + ? where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, quantity);
            pst.setInt(2, cartId);
            i = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        return i;
    }

    // 添加一本图书到购物车
    public int insertOne(CartVo cartVo) {
        int i = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int BookId = cartVo.getBook_id();
        try {
            conn = DatabaseUtil.getConnection();
            String sql1 = "select id from cart where book_id = ?";
            pst = conn.prepareStatement(sql1);
            pst.setInt(1,BookId);
            rs = pst.executeQuery();
            if(rs.next()){
                i = updateQuantity(rs.getInt("id"),1);
            }else {
                String sql = "insert into cart(user_id,book_id,quantity,create_gmt,book_title,book_image_url,book_price)"
                        + "values(?,?,?,?,?,?,?)";
                pst = conn.prepareStatement(sql);
                pst.setInt(1, cartVo.getUser_id() );
                pst.setInt(2, cartVo.getBook_id());
                pst.setInt(3, cartVo.getQuantity());
                pst.setLong(4, cartVo.getCreate_gmt());
                pst.setString(5, cartVo.getBook_title());
                pst.setString(6, cartVo.getBook_image_url());
                pst.setBigDecimal(7, cartVo.getBook_price());
                i = pst.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }

//        try {
//            conn = DatabaseUtil.getConnection();
//            String sql = "insert into cart(user_id,book_id,quantity,create_gmt,book_title,book_image_url,book_price)"
//                    + "values(?,?,?,?,?,?,?)";
//            pst = conn.prepareStatement(sql);
//            pst.setInt(1, cartVo.getUser_id() );
//            pst.setInt(2, cartVo.getBook_id());
//            pst.setInt(3, cartVo.getQuantity());
//            pst.setLong(4, cartVo.getCreate_gmt());
//            pst.setString(5, cartVo.getBook_title());
//            pst.setString(6, cartVo.getBook_image_url());
//            pst.setBigDecimal(7, cartVo.getBook_price());
//            i = pst.executeUpdate();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            DatabaseUtil.closeAll(rs, pst, conn);
//        }
        return i;
    }

    // 购物车中删除一条记录
    public int deleteOne(int cartId) {
        int i = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "delete from cart where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, cartId);

            i = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        return i;
    }
}


